<?php

	namespace application\model;

		require_once ("Recipe.php");

		//Data Acess Layer (DAL) for Books

		class RecipeDALmodel
		{
			private static $tableName = "Recipe";
			//`bokparl_BRC`.`Recipe`
			private $mysqli;
			private $sql;
			private $recipe;

			//@param mysqli  http://www.php.net/manual/en/book.mysqli.php
			public function __construct(\mysqli $mysqli)
			{
				$this->mysqli = $mysqli;

				//http://stackoverflow.com/questions/1707792/issue-with-utf-8-encoding-using-php-mysql
				$mysqli->query("SET NAMES 'utf8'");
			}

			public function createTable()
			{
				$sql = "CREATE TABLE `" . self::$tableName . "` 
            (
                `pk` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                `author` VARCHAR(255),
                `title` VARCHAR(255),
                `isbn` VARCHAR(255)
            )
            ENGINE = MyISAM;";

				if ($this->mysqli->query($sql) === FALSE)
				{
					throw new Exception("'$sql' failed " . $this->mysqli->error);
				}
			}

			//returnerar recepten i en array tillbaka till konstruktorn i
			// RecipeCollectionModel som kallade
			public function getOneRecipe($id)
			{
				$sql = "SELECT 
						RecipeID,RecipeName,Instructions, 
						RecipeType, R.RecipeTypeID, R.AuthorID, Author, RecipeTags
						FROM Recipe AS R 
						INNER JOIN  RecipeType AS RT ON R.RecipeTypeID = RT.RecipeTypeID
						INNER JOIN  Author AS A ON R.AuthorID = A.AuthorID
						WHERE RecipeID = $id";

				$result = $this->monitorIncommingData($sql);
				//http://www.php.net/manual/en/mysqli-result.fetch-array.php
				while ($object = $result->fetch_array(MYSQLI_ASSOC))
				{
					$this->recipe = new Recipe($object["RecipeID"],
											   $object["RecipeName"], 
											   $object["Instructions"], 
											   $object["RecipeType"],
											   $object["RecipeTypeID"], 
											   $object["Author"],
											   $object["AuthorID"],
											   $object["RecipeTags"]);
				}
				return $this->recipe;
			}

			public function getLatestRecipe()
			{
				$sql = "SELECT 
						RecipeID,RecipeName,Instructions, 
						RecipeType, R.RecipeTypeID, R.AuthorID, Author, RecipeTags
						FROM Recipe AS R 
						INNER JOIN  RecipeType AS RT ON R.RecipeTypeID = RT.RecipeTypeID
						INNER JOIN  Author AS A ON R.AuthorID = A.AuthorID
						ORDER BY RecipeID DESC
						LIMIT 1";

				$result = $this->monitorIncommingData($sql);
				//http://www.php.net/manual/en/mysqli-result.fetch-array.php
				while ($object = $result->fetch_array(MYSQLI_ASSOC))
				{
					$recipe = new Recipe($object["RecipeID"],
										$object["RecipeName"], 
										$object["Instructions"], 
										$object["RecipeType"],
										$object["RecipeTypeID"], 
										$object["Author"],
										$object["AuthorID"],
										$object["RecipeTags"]);

				}
				return $recipe;

			}

			//returnerar recepten i en array tillbaka till konstruktorn i
			// RecipeCollectionModel som kallade
			public function getAllRecipes()
			{
				$ret = array();
				$sql = "SELECT 
						RecipeID,RecipeName,Instructions, 
						RecipeType, R.RecipeTypeID, R.AuthorID, Author, RecipeTags
						FROM Recipe AS R 
						INNER JOIN  RecipeType AS RT ON R.RecipeTypeID = RT.RecipeTypeID
						INNER JOIN  Author AS A ON R.AuthorID = A.AuthorID
						ORDER BY RecipeID ASC";

				$result = $this->monitorIncommingData($sql);
				$ret = $this->fetchArrayArray($result);
				return $ret;
				
			}
			
			public function fetchArrayArray($result)
			{	
				//http://www.php.net/manual/en/mysqli-result.fetch-array.php
				while ($object = $result->fetch_array(MYSQLI_ASSOC))
				{
					$ret[] = new Recipe($object["RecipeID"],
									    $object["RecipeName"], 
									    $object["Instructions"], 
									    $object["RecipeType"],
									    $object["RecipeTypeID"], 
									    $object["Author"],
									    $object["AuthorID"],
										$object["RecipeTags"]);
				}
				return $ret;

			}
			
			//SEARCH
			//TODO felhantering på när sökvärdena inte fungerar, är tomma, inte finns
			public function getRecipeListByType($searchRule, $id)
			{
				$ret = array();
				$sql = "SELECT 
						RecipeID,RecipeName,Instructions, 
						RecipeType, R.RecipeTypeID, R.AuthorID, Author, RecipeTags
						FROM Recipe AS R 
						INNER JOIN  RecipeType AS RT ON R.RecipeTypeID = RT.RecipeTypeID
						INNER JOIN  Author AS A ON R.AuthorID = A.AuthorID
						WHERE R.$searchRule = $id
						ORDER BY RecipeID ASC";

				$result = $this->monitorIncommingData($sql);

				$ret = $this->fetchArrayArray($result);
				return $ret;
			}

			public function getRecipeListByText($searchRule, $text)
			{
		
				$ret = array();
				$sql = "SELECT 
						RecipeID,RecipeName,Instructions, 
						RecipeType, R.RecipeTypeID, R.AuthorID, Author, RecipeTags
						FROM Recipe AS R 
						INNER JOIN  RecipeType AS RT ON R.RecipeTypeID = RT.RecipeTypeID
						INNER JOIN  Author AS A ON R.AuthorID = A.AuthorID
						WHERE R.$searchRule LIKE \"%$text%\"
						ORDER BY RecipeID ASC";

				$result = $this->monitorIncommingData($sql);

				$ret = $this->fetchArrayArray($result);
				return $ret;
			}

			//ADMIN
			public function deleteRecipe($recipeId)
			{

				$sqlDelete = "DELETE FROM 
				" . self::$tableName . " 
	            WHERE " . self::$tableName . ".`RecipeID` =" . $recipeId . "
	            LIMIT 1";

				//http://www.php.net/manual/en/mysqli-stmt.prepare.php
				$statement = $this->mysqli->prepare($sqlDelete);

				if ($statement === FALSE)
				{
					throw new Exception("prepare of $sql failed " . $this->mysqli->error);
				}

				//http://www.php.net/manual/en/mysqli-stmt.execute.php
				if ($statement->execute() === FALSE)
				{
					throw new Exception("execute of $sql failed " . $statement->error);
				}
			}

			public function updateRecipe($recipe)
			{

				$RecipeId = $recipe->getRecipeID();
				$RecipeName = $recipe->getRecipeName();
				$Instructions = $recipe->getRecipeInstructions();
				$RecipeTypeID = $recipe->getRecipeTypeID();
				$AuthorID = $recipe->getRecipeAuthorID();
				$RecipeTags = $recipe->getRecipeTags();

				$sqlUpdate = "UPDATE  " . self::$tableName . "
					 SET  
					 	`RecipeName` =  ?,
						`Instructions` =  ?,
						`RecipeTypeID` =  ?,
						`AuthorID` =  ?,
						`RecipeTags` =  ?
						WHERE  
						`Recipe`.`RecipeID` = $RecipeId";
		
				$this->monitorOutGoingData($sqlUpdate, $RecipeName, $Instructions, $RecipeTypeID, $AuthorID, $RecipeTags);
			}

			public function saveRecipe(Recipe $recipe)
			{
				$RecipeName = $recipe->getRecipeName();
				$Instructions = $recipe->getRecipeInstructions();
				$RecipeTypeID = $recipe->getRecipeTypeID();
				$AuthorID = $recipe->getRecipeAuthorID();
				$RecipeTags = $recipe->getRecipeTags();

				$sqlInsert = "INSERT INTO " . self::$tableName . " 
	            (
	                RecipeName, 
					Instructions, 
					RecipeTypeID, 
					AuthorID,
					RecipeTags
	            ) 
	            VALUES(?, ?, ?, ?, ?)";

				$this->monitorOutGoingData($sqlInsert, $RecipeName, $Instructions, $RecipeTypeID, $AuthorID, $RecipeTags);
			}

			public function monitorIncommingData($sql)
			{
				//http://www.php.net/manual/en/mysqli-stmt.prepare.php
				$statement = $this->mysqli->prepare($sql);
				if ($statement === FALSE)
				{
					throw new Exception("prepare of $sql failed " . $this->mysqli->error);
				}
				//http://www.php.net/manual/en/mysqli-stmt.execute.php
				if ($statement->execute() === FALSE)
				{
					throw new Exception("execute of $sql failed " . $statement->error);
				}
				//http://www.php.net/manual/en/mysqli-stmt.get-result.php
				$result = $statement->get_result();
				return $result;
			}
			
			//TODO ingen fångar nedan exeptions
			public function monitorOutGoingData($sql, $RecipeName, $Instructions, $RecipeTypeID, $AuthorID, $RecipeTags)
			{
				$this->sql = $sql;

				//http://www.php.net/manual/en/mysqli-stmt.prepare.php
				$statement = $this->mysqli->prepare($sql);
				if ($statement === FALSE)
				{
					throw new Exception("prepare of $sql failed " . $this->mysqli->error);
				}
				//http://www.php.net/manual/en/mysqli-stmt.bind-param.php
				if ($statement->bind_param("ssiis", $RecipeName, $Instructions, $RecipeTypeID, $AuthorID, $RecipeTags) === FALSE)
				{
					throw new Exception("bind_param of $sql failed " . $statement->error);
				}

				//http://www.php.net/manual/en/mysqli-stmt.execute.php
				if ($statement->execute() === FALSE)
				{
					throw new Exception("execute of $sql failed " . $statement->error);
				}
			}


		}
